import time
from . import ConnPG
import psycopg2
from sanic.log import logger


class SkuAccountTable:

    '''目前只有入库订单逻辑'''

    # 入库定单添加一个sku #增加入库单产品条目
    async def add_sku_account_in(self, sku_id, storage_location_id,
                                 quantity, odd_id, price, in_or_out=True):  # 入库订单添加sku

        # 参数检查
        if not all([in_or_out, sku_id, storage_location_id, quantity, odd_id]):

            return {
                'status': -1,
                'errorCode': 305100,
                'errorMsg': '检查参数',
                'resultBody': None
            }

        if price != None:

            try:
                price = float(price)

            except ValueError as e:

                return{
                    'status': -1,
                    'errorCode': 305101,
                    'errorMsg': '商品单价应该为浮点类型',
                    'resultBody': None,
                }

        sql = '''\
        INSERT INTO sku_account(
            in_or_out, sku_id, storage_location_id, quantity,
            odd_id, price, create_time
            )
        VALUES (
            %s, %s, %s, %s, %s, %s, %s)

        RETURNING id'''

        try:
            result = await ConnPG().fetchone(
                sql,
                (
                    in_or_out, sku_id, storage_location_id,
                    quantity, odd_id, price, int(time.time())
                )

            )
        except psycopg2.errors.ForeignKeyViolation as e:

            return {
                'status': -1,
                'errorCode': 305102,
                'errorMsg': str(e),
                'resultBody': None,
            }
        else:

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': result,
            }

    # 出库定单添加一个sku #增加入库单产品条目
    async def add_sku_account_out(self, sku_id, storage_location_id,
                                 quantity, odd_id, price, in_or_out=False):  # 入库订单添加sku

        # 参数检查
        if not all([sku_id, storage_location_id, quantity, odd_id]):

            return {
                'status': -1,
                'errorCode': 305100,
                'errorMsg': '检查参数',
                'resultBody': None
            }

        if price != None:

            try:
                price = float(price)

            except ValueError as e:

                return{
                    'status': -1,
                    'errorCode': 305101,
                    'errorMsg': '商品单价应该为浮点类型',
                    'resultBody': None,
                }

        sql = '''\
        INSERT INTO sku_account(
            in_or_out, sku_id, storage_location_id, quantity,
            odd_id, price, create_time
            )
        VALUES (
            %s, %s, %s, %s, %s, %s, %s)

        RETURNING id'''

        try:
            result = await ConnPG().fetchone(
                sql,
                (
                    in_or_out, sku_id, storage_location_id,
                    quantity, odd_id, price, int(time.time())
                )

            )
        except psycopg2.errors.ForeignKeyViolation as e:

            return {
                'status': -1,
                'errorCode': 305102,
                'errorMsg': str(e),
                'resultBody': None,
            }
        else:

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': result,
            }

    # 更新一个入库sku_account
    async def update_sku_account_in(self, sku_account_id, sku_id, storage_location_id,
                                 quantity, odd_id, price, in_or_out=True):  # 入库订单添加sku

        # 参数检查
        if not all([sku_account_id, in_or_out, sku_id, storage_location_id, quantity, odd_id]):

            return {
                'status': -1,
                'errorCode': 305100,
                'errorMsg': '检查参数',
                'resultBody': None
            }

        if price != None:

            try:
                price = float(price)

            except ValueError as e:

                return{
                    'status': -1,
                    'errorCode': 305101,
                    'errorMsg': '商品单价应该为浮点类型',
                    'resultBody': None,
                }

        check_sql = 'select * from sku_account where id=%s'

        check_result = await ConnPG().fetchone(check_sql, (sku_account_id,))

        if not check_result:

            return {
                'status': -1,
                'errorCode': 305102,
                'errorMsg': '没有找到对应id的sku_account',
                'resultBody': None,
            }

        if check_result['status'] == 'success':

            return {
                'status': -1,
                'errorCode': 305103,
                'errorMsg': '已经是完成状态无法更新',
                'resultBody': None,
            }

        if check_result['status'] == 'delete':

            return {
                'status': -1,
                'errorCode': 305104,
                'errorMsg': '已经是删除状态无法更新',
                'resultBody': None,
            }

        sql = '''\
        UPDATE
            sku_account
        SET
            in_or_out=%s,
            sku_id=%s,
            storage_location_id=%s,
            quantity=%s,
            odd_id=%s,
            price=%s,
            create_time=%s
        WHERE
            id=%s
        '''

        await ConnPG().execute(
            sql,
            (
                in_or_out, sku_id, storage_location_id,
                quantity, odd_id, price, int(time.time()), sku_account_id
            )
        )

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': "更新成功",
        }

    # sku_account 确认

    async def sku_account_success_in(self, sku_account_id):

        sql = "update sku_account set status='success' where id=%s"

        check_sql = 'select * from sku_account where id=%s'

        check_info = await ConnPG().fetchone(check_sql, (sku_account_id,))

        if check_info == None:  # 订单不存在

            return {
                'status': -1,
                'errorCode': 303102,
                'errorMsg': '没有该订单',
                'resultBody': None,
            }

        # print(check_info)

        await ConnPG().execute(sql, (sku_account_id,))

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': "更新成功",
        }

        # if check_info['status'] == False:

    async def select_sku_info(self, sku_id):#查看指定sku_id的信息

        if not sku_id or not sku_id.isdecimal():
            return {
                'status': -1,
                'errorCode': 112100,
                'errorMsg': '请检查参数合法性',
                'resultBody': None
            }

        sql = 'select * from view_sku_info where id=%s'

        result = await ConnPG().fetchall(sql, (sku_id,))

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }

    # sku_account 确认
    async def select_sku_account_by_odd_id(self, odd_id):

        if odd_id == None:

            return {

                'status': -1,
                'errorCode': 303103,
                'errorMsg': '本接口需要一个odd_id的必传参数',
                'resultBody': None,

            }

        sql = "select * from  view_sku_account where odd_id=%s"


        result = await ConnPG().fetchall(sql, (odd_id,))

        for i in result:

            i['sku_attribute'] = await self.select_sku_info(str(i['sku_id']))
        
        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }
    
    async def select_view_sku_account__by_house_id_and_sku_id(self, house_id, sku_id):

        if not all([house_id, sku_id]):

            return {

                'status': -1,
                'errorCode': 303103,
                'errorMsg': '本接口需要house_id, sku_id的必传参数',
                'resultBody': None,

            }

        sql = "select * from  view_sku_account_ where warehouse_id=%s and sku_id=%s"


        result = await ConnPG().fetchall(sql, (house_id, sku_id))


        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }
        
        # if check_info['status'] == False:

     
    async def select_all_sum_view_sku_account(self):

        sql = "select * from  sum_view_sku_account"

        result = await ConnPG().fetchall(sql)

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }
    
    async def select_view_sku_account_by_storage_location_id(self, storage_location_id):

        sql = "select * from view_sku_account_ where storage_location_id = %s"

        result = await ConnPG().fetchall(sql, (storage_location_id,))

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': result,
        }

    
    async def select_all_sum_view_sku_account_by_sku_id(self, sku_id):

        if sku_id == None:

            return {

                'status': -1,
                'errorCode': 303103,
                'errorMsg': '本接口需要一个sku_id的必传参数',
                'resultBody': None,

            }
        
        sql = "select * from  sum_view_sku_account where sku_id=%s"

        result = await ConnPG().fetchall(sql, (sku_id,))

        # print(result)

        warehouse_dict = {i['warehouse_name']:[] for i in result}

        count = warehouse_dict.copy()
        for i in result:

            warehouse_dict[i['warehouse_name']].append(dict(i))
        
        for key, value in warehouse_dict.items():

            out_num = 0

            for i in value:

                if i['in_or_out'] == True:

                    in_num = i['sum']
                
                if i['in_or_out'] == False:

                    out_num = i['sum']

            count[key] = in_num - out_num 

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': {"info": warehouse_dict, 'count': count},
        }

    # 当前产品所在位置的详细情况
    async def select_sku_in_where_location(self, warehouse_id, sku_id):


        if not all([warehouse_id, sku_id]):

            return {

                'status': -1,
                'errorCode': 303105,
                'errorMsg': '本接口需要warehouse_id, sku_id两个必传参数',
                'resultBody': None,

            }

        sql = '''

        select 
            sum(quantity),warehouse_id,location_name,
            in_or_out,sku_id, storage_location_id,warehouse_name 
        from 
            view_sku_account 
        group by 
        
            warehouse_id,storage_location_id,
            in_or_out,sku_id,location_name,
            warehouse_name 
        having warehouse_id=%s and sku_id=%s;
        '''
        # sql = "select * from view_sku_account where warehouse_id=%s and sku_id=%s"

        
        result = await ConnPG().fetchall(sql,(warehouse_id, sku_id))

        #所有库位列表
        location_list = list(set([i['storage_location_id'] for i in result]))


        result_list = []

        #迭代库位
        for i in location_list:
            #库位产品进总和
            in_sum = sum([ii['sum'] for ii in result if ii['storage_location_id'] == i and ii['in_or_out']==True])
            #库位产品出总和
            out_sum = sum([ii['sum'] for ii in result if ii['storage_location_id'] == i and ii['in_or_out']==False])
            #如果该库位进出差不等于0，就是改货位现有的数量，添加到结果列表中
            if in_sum - out_sum > 0:
                for x in result:#取货位名子友好前端
                    if x['storage_location_id'] == i:
                        location_name = x['location_name']
                        break

                result_list.append({'location_id': i, 'sum': in_sum - out_sum, 'location_name': location_name})

        # print(result_list)

        #该产品在库中的总数
        sku_in_warehouse_sum = sum([i['sum'] for i in result_list])


        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': {'sku_in_warehouse_sum': sku_in_warehouse_sum, 'detail': result_list},
        }

    

    


